package com.CommunityHub.kiana.dao;


import com.CommunityHub.kiana.po.VenueInfo;
import com.CommunityHub.kiana.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class VenueDAO {
    public List<VenueInfo> getAllVenues() throws SQLException {
        List<VenueInfo> venues = new ArrayList<>();
        String sql = "SELECT * FROM Venue_Info";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                VenueInfo venue = new VenueInfo();
                venue.setVenueId(rs.getInt("venue_id"));
                venue.setVenueName(rs.getString("venue_name"));
                venue.setAddress(rs.getString("address"));
                venue.setSpecialNote(rs.getString("special_note"));
                venue.setOpeningTime(rs.getTime("opening_time"));
                venue.setClosingTime(rs.getTime("closing_time"));
                venue.setContactPhone(rs.getString("contact_phone"));
                venue.setUsageStatus(rs.getInt("usage_status"));
                venue.setLocation(rs.getString("location"));
                venue.setSize(rs.getBigDecimal("size"));
                venue.setVenueType(rs.getString("venue_type"));
                venue.setResponsibleId(rs.getInt("responsible_id"));
                venue.setChargeStandard(rs.getBigDecimal("charge_standard"));
                venue.setPublishStatus(rs.getInt("publish_status"));
                venues.add(venue);
            }
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return venues;
    }

    public boolean addVenue(VenueInfo venue) throws SQLException {
        String sql = "INSERT INTO Venue_Info (venue_name, address, special_note, opening_time, closing_time, contact_phone, usage_status, location, size, venue_type, responsible_id, charge_standard, publish_status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, venue.getVenueName());
            pstmt.setString(2, venue.getAddress());
            pstmt.setString(3, venue.getSpecialNote());
            pstmt.setTime(4, venue.getOpeningTime());
            pstmt.setTime(5, venue.getClosingTime());
            pstmt.setString(6, venue.getContactPhone());
            pstmt.setInt(7, venue.getUsageStatus());
            pstmt.setString(8, venue.getLocation());
            pstmt.setBigDecimal(9, venue.getSize());
            pstmt.setString(10, venue.getVenueType());
            pstmt.setObject(11, venue.getResponsibleId());
            pstmt.setBigDecimal(12, venue.getChargeStandard());
            pstmt.setInt(13, venue.getPublishStatus());

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public boolean updateVenue(VenueInfo venue) throws SQLException {
        String sql = "UPDATE Venue_Info SET venue_name=?, address=?, special_note=?, opening_time=?, closing_time=?, contact_phone=?, usage_status=?, location=?, size=?, venue_type=?, responsible_id=?, charge_standard=?, publish_status=? WHERE venue_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, venue.getVenueName());
            pstmt.setString(2, venue.getAddress());
            pstmt.setString(3, venue.getSpecialNote());
            pstmt.setTime(4, venue.getOpeningTime());
            pstmt.setTime(5, venue.getClosingTime());
            pstmt.setString(6, venue.getContactPhone());
            pstmt.setInt(7, venue.getUsageStatus());
            pstmt.setString(8, venue.getLocation());
            pstmt.setBigDecimal(9, venue.getSize());
            pstmt.setString(10, venue.getVenueType());
            pstmt.setObject(11, venue.getResponsibleId());
            pstmt.setBigDecimal(12, venue.getChargeStandard());
            pstmt.setInt(13, venue.getPublishStatus());
            pstmt.setInt(14, venue.getVenueId());

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public boolean deleteVenue(int venueId) throws SQLException {
        String sql = "DELETE FROM Venue_Info WHERE venue_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, venueId);

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public int getAllCountVenues() throws SQLException {
        String sql = "SELECT COUNT(*) FROM Venue_Info";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.next()) {
                return rs.getInt(1); // 获取第一列的计数结果
            }

        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return 0;
    }
}